﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;

namespace ProrabHelp.Library
{
    public class ServerObject
    {
        DataBridge mDataBridge = null;
        private DataTable mTableNames;
        private DataTable mTableFieldNames;

        public ServerObject()
        {
            mDataBridge = new DataBridge();

            string commandTableList = @"SELECT " + UFT.Table_TableList.COLUMN_TableID
                                            + ", " + UFT.Table_TableList.COLUMN_TableNameDB
                                            + " FROM " + UFT.Table_TableList.TABLE_NAME;

            string commandFieldList = @"SELECT " + UFT.Table_FieldsList.COLUMN_FieldID
                                            + ", " + UFT.Table_FieldsList.COLUMN_FieldNameDB
                                            + " FROM " + UFT.Table_FieldsList.TABLE_NAME;

            if (mDataBridge.Connect() == true)
            {
                mTableNames = mDataBridge.ExecuteQuery(commandTableList);
                //mLastError = mDataBridge.LastError;

                mTableFieldNames = mDataBridge.ExecuteQuery(commandFieldList);
                //mLastError = mDataBridge.LastError;
                mDataBridge.Disconnect();
            }      
        }
        
        private string GetTableName(int tableID)
        {
            string tableName = string.Empty;
            if (mTableNames != null && mTableNames.Rows.Count > 0)
            {
                string expression = UFT.Table_TableList.COLUMN_TableID + " = " + tableID;
                DataRow[] selectedRows = mTableNames.Select(expression);
                if (selectedRows != null && selectedRows.Length == 1)
                {
                    tableName = (string)selectedRows[0].ItemArray[1];
                }
                else
                {
                    throw new Exception("Multiple values for one table name code!");
                }

            }
            return tableName;
        }

        private string GetColumnName(int columnID)
        {
            string columnName = string.Empty;
            if (mTableFieldNames != null && mTableFieldNames.Rows.Count > 0)
            {
                string expression = UFT.Table_FieldsList.COLUMN_FieldID + " = " + columnID;
                DataRow[] selectedRows = mTableFieldNames.Select(expression);
                if (selectedRows != null && selectedRows.Length == 1)
                {
                    columnName = (string)selectedRows[0].ItemArray[1];
                }
                else
                {
                    throw new Exception("Multiple values for one table name code!");
                }

            }
            return columnName;
        }

        public bool AutentificateUser(string username, string password)
        {
            bool result = false;
            DataTable returnTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT UserID "
                                            + " FROM USERS "
                                            + " WHERE Login ='" + username + "' "
                                            + " AND Password ='" + password + "' ";

                    returnTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            if (returnTable != null && returnTable.Rows.Count > 0) result = true;

            return result;
        }

        public void CloseThisTab(UserControl MyPpage)
        {
            TabPage tabpage = (TabPage)MyPpage.Parent;
            TabControl tabControl = (TabControl)tabpage.Parent;

            tabControl.TabPages.Remove(tabpage);
        }


        #region CompanyList

        public DataTable GetCompanyList(bool onlyActive)
        {
            DataTable resultTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT
                                             Company.id                                            
                                            , Company.Name 
                                            , Company.Short_name                                           
                                            , Company.Address
                                            , Company.isDeleted

                                            FROM Company ";
                    if (onlyActive) commandText += " Where  Company.isDeleted = False";
                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return resultTable;
        }

        public bool AddCompany(string companyName, string shortName, string address, bool isDeleted)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Company ([Name], Short_name, Address, isDeleted) "
                        + "VALUES ( "
                        + " '" + companyName + "' "
                        + ", '" + shortName + "' "
                        + ", '" + address + "' "
                        + ", " + isDeleted + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateCompany(int companyID, string companyName, string shortName, string address, bool isDeleted)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Company SET "
                      + " [Name] = '" + companyName + "' "
                      + " , Short_name = '" + shortName + "' "
                      + " , Address = '" + address + "' "
                      + " , isDeleted = " + isDeleted + " "
                      + " Where id = " + companyID + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool DeleteCompany(int comoanyID)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Company SET "
                      + " isDeleted = True "
                      + " Where id = " + comoanyID + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
 
        }

        #endregion CompanyList

        #region ClassifierType

        public bool AddClassifierType(string name, bool isSystemClass)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO ClassifierTypes (Name, isDelete, isSystemClassifier) "
                        + "VALUES ( "
                        + " '" + name + "' "
                        + ", False "
                        + ", " + isSystemClass + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateClassifierType(int typeID, string nameType, bool isSystemClass)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE ClassifierTypes "
                            + "SET ClassifierTypes.[Name] = '" + nameType + "' "
                            + ",  ClassifierTypes.[isSystemClassifier] = " + isSystemClass + " "
                            + " WHERE ClassifierTypes.TypeID = " + typeID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable SelectTypeClassifiers(bool isDelete, bool isSystemClassifier)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT TypeID "
                        + ", Name "
                        + " FROM ClassifierTypes "
                        + " WHERE isDelete = " + Convert.ToInt32(isDelete) + " "
                        + " AND isSystemClassifier = " + isSystemClassifier + " "
                        + " ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        #endregion ClassifierType

        #region Classifiers

        public bool AddClassifiers(int typeID, string name, string description)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Classifiers (TypeID, [Name], Description, isDelete) "
                        + "VALUES ( "
                        + typeID
                        + ", '" + name + "' "
                        + ", '" + description + "' "
                        + ", False "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateClassifiers(int codeID, int typeID, string nameClassifiers, string description)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Classifiers "
                            + "SET Classifiers.[TypeID] = " + typeID + " "
                            + ", Classifiers.[Name] = '" + nameClassifiers + "' "
                            + ", Classifiers.[Description] ='" + description + "' "
                            + " WHERE Classifiers.CodeID = " + codeID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable GetClassifiersListByTypeID(int typeID)
        {
            DataTable classifierTypesByIDList = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Code) + "] "
                                         + ", Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Name) + "] "
                                         + ", Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Description) + "] "
                                         + " FROM " + GetTableName(UFT.Table_Classifiers.TABLE_NAME) + " as Classifiers "
                                        + " WHERE Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_TypeID) + "] = " + typeID + " ";
                    commandText += " ORDER BY Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Name) + "] asc ";
                    classifierTypesByIDList = mDataBridge.ExecuteQuery(commandText);
                    // mLastError = mDataBridge.LastError;
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return classifierTypesByIDList;
        }

        public DataTable SelectClassifiers(int typeID, bool isDelete, bool isSystemClassifier)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT Classifiers.CodeID "
                        + ", Classifiers.Name "
                        + ", Classifiers.Description "
                        + ", ClassifierTypes.Name AS Grupe "
                        + " FROM Classifiers INNER JOIN ClassifierTypes "
                        + " ON Classifiers.TypeID = ClassifierTypes.TypeID "
                        + " WHERE Classifiers.isDelete = " + Convert.ToInt32(isDelete) + " "
                        + " ";
                    if (typeID > 1)
                    {
                        commandText += " AND Classifiers.TypeID = " + typeID;
                        commandText += " AND ClassifierTypes.isSystemClassifier = " + isSystemClassifier;
                    }

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        #endregion Classifiers       

        #region Users

        public DataTable GetUserList()
        {
            DataTable result = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT 
                                            Users.UserID, 
                                            Users.FirstName, 
                                            Users.LastName, 
                                            Users.Login, 
                                            Users.Password, 
                                            Users.isDelete, 
                                            Users.RoleType,
                                            Users.PasswordStatus,
                                            Classifiers.Name  as [Role Type], 
                                            Classifiers_1.Name as [Password Status]
                                            FROM Classifiers AS Classifiers_1 INNER JOIN 
                                                (Classifiers INNER JOIN Users 
                                                    ON Classifiers.CodeID = Users.RoleType) 
                                                ON Classifiers_1.CodeID = Users.PasswordStatus; ";
                    result = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool AddUser(string FirstName, string LastName, string Login, string Password, bool isDelete, int RoleType, int PasswordStatus)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Users ([FirstName], [LastName], [Login], [Password], [isDelete], [RoleType], [PasswordStatus]) "
                        + "VALUES ( "
                        + "  '" + FirstName + "'  "
                        + ", '" + LastName + "' "
                        + ", '" + Login + "' "
                        + ", '" + Password + "' "
                        + ", " + isDelete + " "
                        + ", " + RoleType + " "
                        + ", " + PasswordStatus + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateUser(int UserID, string FirstName, string LastName, string Login, string Password, bool isDelete, int RoleType, int PasswordStatus)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Users "
                            + " SET FirstName = '" + FirstName + "' "
                            + ", LastName = '" + LastName + "' "
                            + ", [Login] = '" + Login + "' "
                            + ", [Password] = '" + Password + "' "
                            + ", [isDelete] = " + isDelete + " "
                            + ", [RoleType] = " + RoleType + " "
                            + ", PasswordStatus = " + PasswordStatus + " "
                            + " WHERE UserID = " + UserID + "; "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable GetUserByID(int userID)
        {
            DataTable resultTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT * From Users WHERE UserID = " + userID + "; ";
                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return resultTable;
        }

        public bool DeleteUser(int userID)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"Delete From Users WHERE UserID = " + userID + "; ";
                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }


        #endregion Users       

        #region Remains
        public bool AddRemains()
        {
            bool result = false;

            //try
            //{
            //    if (mDataBridge.Connect() == true)
            //    {
            //        string nonQuery = @"INSERT INTO Classifiers (TypeID, [Name], Description, isDelete) "
            //            + "VALUES ( "
            //            + typeID
            //            + ", '" + name + "' "
            //            + ", '" + description + "' "
            //            + ", 'False' "
            //            + " ) ";

            //        result = mDataBridge.ExecuteNonQuery(nonQuery);
            //    }
            //}
            //finally
            //{
            //    mDataBridge.Disconnect();
            //}

            return result;
        }

        public bool UpdateRemains()
        {
            bool result = false;

            //try
            //{
            //    if (mDataBridge.Connect() == true)
            //    {
            //        string nonQuery = @" UPDATE Classifiers "
            //                + "SET Classifiers.[TypeID] = " + typeID + " "
            //                + ", Classifiers.[Name] = '" + nameClassifiers + "' "
            //                + ", Classifiers.[Description] ='" + description + "' "
            //                + " WHERE Classifiers.CodeID = " + codeID + " "
            //                + " ";

            //        result = mDataBridge.ExecuteNonQuery(nonQuery);
            //    }
            //}
            //finally
            //{
            //    mDataBridge.Disconnect();
            //}

            return result;
        }

        public bool DeleteRemains()
        {
            bool result = false;
            return result;
        }

        public DataTable GetRemainsList()
        {
            DataTable resultTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT
                                             Remains.id
                                            , Products.Description
                                            , Classifiers.Name as [Measure Unit]
                                            , Remains.quantity
                                            , Remains.id_firma
                                            , Remains.cost_per_unit
                                            , Remains.cost_per_unit * Remains.quantity as [Total Cost]

                                            FROM Remains 
                                                INNER JOIN Products
                                            ON Remains.id_Product = Products.id 
                                                INNER JOIN Classifiers  
                                            ON Products.MeasureUnit = Classifiers.CodeID; ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return resultTable;
        }

        public DataTable SelectRemain(int remID, bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT * FROM Remains  "
                        + " WHERE Remains.isDelete = " + Convert.ToInt32(isDelete) + " "
                        + " AND Remains.id = " + remID + "  ";                   

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public bool AddRemains(int id_product, double quantity, double cost_per_unit, int id_invoice)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO RemainsHistory (id_product, version, quantity, cost_per_unit, id_invoice, edit_date) "
                            + "VALUES ( "
                            + " " + id_product + " "
                            + ", " + 1 + " "
                            + ", " + quantity + " "
                            + ", " + cost_per_unit + " "
                            + ", " + id_invoice + " "
                            + ", '" + DateTime.Now.ToString("yyyy/mm/dd") + "' "
                            + ") ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }


        #endregion Remains

        #region Products

        public DataTable GetProductList()
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = 
                           @" SELECT Products.id "
                        + " , Products.Description "
                        + " , Classifiers.Name as MeasureUnit "
                        + " FROM Products INNER JOIN Classifiers  "
                        + "     ON Products.MeasureUnit = Classifiers.CodeID;";
                   
                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataTable GetProductByID(int idProduct)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT Products.id "
                        + ", Products.Name "
                        + ", Products.Description "
                        + " FROM Products "
                        + " Where Products.id = " + idProduct;

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public bool AddProduct(int measureUnit, string description)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Products (Description, MeasureUnit) "
                        + "VALUES ( "
                        + " '" + description + "' "
                        + ", " + measureUnit + ") ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateProduct(int idProduct, int measureUnit, string description)
        {
            bool result = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"UPDATE Products SET "
                        + " Description = '" + description + "' "
                        + ", MeasureUnit = " + measureUnit + " "
                        + " WHERE id = " + idProduct + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        #endregion Products
                
        #region Opearing With Stoc

        public DataTable GetCompanyListPrestedProduct(int idProduct)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT 
                                            Company.id
                                            , Company.Name
                                            , Company.Address
                                            FROM Company INNER JOIN CompanyProduct 
                                                ON Company.id = CompanyProduct.CompanyID 
                                            WHERE CompanyProduct.ProductID = idProduct; ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }


        #endregion Opearing With Stoc
    }
}
